package com.syxx.oa.dao;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.syxx.oa.domain.Department;
import com.syxx.oa.domain.Employee;
import com.syxx.oa.utils.StringUtils;
import com.test.jbpm.Pager;
import com.test.jbpm.dao.impl.BaseDao;
import com.test.jbpm.domain.User;
@Repository  //代表存储层bean
public class EmployeeDao extends BaseDao {

	public void addOrUpdate(Employee emp) {
		try {
			getHibernateTemplate().saveOrUpdate(emp);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void del(final Long empId) {
		Employee emp = (Employee)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException,SQLException {
				return session.createQuery("from Employee d where d.empId=?")
					.setParameter(0, empId)
					.uniqueResult();
			}	
		});
		getHibernateTemplate().delete(emp);
	}

	public Employee findEmpById(final Long empId) {
		
		return (Employee)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				return session.createQuery("from Employee d where d.empId=?")
							.setParameter(0, empId)
							.uniqueResult();
			}
		});
	}

	@SuppressWarnings("unchecked")
	public Pager<Employee> findEmpList() {
		return findData("from Employee");
	}
	
	public Pager<Employee> findEmpListByDep(Long depId){
		return null;
	}
	
	public Employee login(final String cName,final String cPassword){
		return (Employee)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				return session.createQuery("from Employee e where e.empLoginName=? and e.empPassword=?")
							.setParameter(0, cName)
							.setParameter(1, cPassword)
							.uniqueResult();
			}
		});
	}
	/**
	 * 通过部门Id和角色Id查找相应职员信息
	 * @param levId
	 * @param depId
	 * @return
	 */
	public List<Employee> findEmpByLevADep(final String levId,final String depId){
		@SuppressWarnings("unchecked")
		List<Employee> list = getHibernateTemplate().executeFind(new HibernateCallback() {
			
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String hql = " from Employee e where (e.empLevel.levCode=?) and (e.department.depCode=?)";
				Query query = session.createQuery(hql);
				query.setParameter(0, levId);
				query.setParameter(1, depId);
				return query.list();
			}
		});
		return list;
	}
	
	/**
	 * 通过部门编码查找相应职员信息
	 * @param depCode
	 * @return
	 */
	public List<Employee> findEmpByDepCode(final String depCode){
		@SuppressWarnings("unchecked")
		List<Employee> list = getHibernateTemplate().executeFind(new HibernateCallback() {
			
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String hql = " from Employee e where (e.department.depCode=?)";
				Query query = session.createQuery(hql);
				query.setParameter(0, depCode);
				return query.list();
			}
		});
		return list;
	}
	
	
	
	/**
	 * 查询上级部门的职员信息
	 * @return
	 */
	public List<Employee> findParentEmpByDep(final Long depId){
		Department dep = (Department) getHibernateTemplate().load(Department.class, depId);
		final Department parDep = dep.getParent();
		if(parDep == null){
			return null;
		}
		
		@SuppressWarnings("unchecked")
		List<Employee> list = getHibernateTemplate().executeFind(new HibernateCallback() {
			
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String hql = "from Employee e where (e.department.depId=?)";
				Query query = session.createQuery(hql);
				query.setParameter(0, parDep.getDepId());
				return query.list();
			}
		});
		return list;
	}
	/**
	 * 查找职位最高的职员
	 * @return
	 */
	public Employee findMaxLevEmpByEmp(Employee key){
		if(key == null || key.getDepartment() == null){
			return null;
		}
		final Long depId = key.getDepartment().getDepId();
		
		
		return (Employee)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				String hql = "from Employee e where (e.department.depId=?) order by e.empLevel.levCode";
				Query q = session.createQuery(hql);
				q.setParameter(0, depId);
				q.setMaxResults(1);
				return q.uniqueResult();				
			}
		});
		
	}
	
	/**
	 * 通过部门编码查找职位最高的职员
	 * @param key
	 * @return
	 */
	public Employee findMaxLevByDepCode(final String depCode){
		if(StringUtils.isBlank(depCode)){
			return null;
		}
		
		return (Employee)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				String hql = "from Employee e where (e.department.depCode=?) order by e.empLevel.levCode";
				Query q = session.createQuery(hql);
				q.setParameter(0, depCode);
				q.setMaxResults(1);
				return q.uniqueResult();				
			}
		});
		
	}
	
	/**
	 * 根据角色编码查找职员信息
	 * @param levCode
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Employee> findEmpsBylevCode(final String levCode){
		if(StringUtils.isBlank(levCode)){
			return null;
		}
		
		return (List<Employee>)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				String hql = "from Employee e where (e.empLevel.levCode=?) order by e.empLevel.levCode";
				Query q = session.createQuery(hql);
				q.setParameter(0, levCode);
				return q.list();				
			}
		});
		
	}
	
	
}
